package com.bringway.framework.excel;


import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import net.sf.json.JSONException;
import net.sf.json.JSONObject;


public class ExcelOperUtil {

	/**
	 * 
	 * @param list
	 * @param outputStream
	 */
	public static void writeExcel(List list, OutputStream outputStream) {
		if (list == null || list.size() == 0) {
			return;
		}
		try {
			WritableWorkbook writeWork = Workbook.createWorkbook(outputStream); // 新建工作薄

			for (int i = 0; i < list.size(); i++) {
				ExcelSheet sheet = (ExcelSheet) list.get(i);
				String sheetName = sheet.getSheetName();
				List headerList = sheet.getHeaderList();
				List bodyList = sheet.getBodyList();
				int fromRow = sheet.getFromRow();

				if (sheetName == null || "".equals(sheetName)) {
					sheetName = "sheet" + i;
				}

				WritableSheet outSheet = writeWork.createSheet(sheetName, i); // 新建工作sheet

				if (headerList != null) {
					for (int r = 0; r < headerList.size(); r++) {
						ExcelCell cell = (ExcelCell) headerList.get(r);
						Label lable = new Label(cell.getX(), cell.getY(), cell
								.getValue());
						outSheet.addCell(lable);
					}
				}

				if (bodyList != null) {
					for (int j = 0; j < bodyList.size(); j++) {
						String dataLine[] = (String[]) bodyList.get(j);

						for (int k = 0; k < dataLine.length; k++) {
							Label lable = new Label(k, j + fromRow, dataLine[k]);
							outSheet.addCell(lable);
						}
					}
				}
			}
			// outSheet.mergeCells(r,0,r,3); //合并单元格，参数格式（开始列，开始行，结束列，结束行）
			writeWork.write();
			writeWork.close();
			outputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	


	/**
	 * @param stencilStream
	 *            Excel模版Stream
	 * @param sheetList
	 *            sheet对象List,其中list里存放ExcelSheet，每个ExcelSheet里存放fromRow(表体从第几行写） @
	 *            sheetName;headList;dataList;
	 * @其中headList 里存放ExcelCell，每个ExcelCell存放x， y， value;
	 * @dataList 里存放一条记录的数组
	 * @param targetUrl
	 *            生成的excel的目标目录(c:\\test1.xls)
	 */
	public void writeToExcelByStencil(InputStream stencilStream,
			List sheetList, OutputStream os) {

		try {
			Workbook wb = Workbook.getWorkbook(stencilStream);

			ByteArrayOutputStream targetFile = new ByteArrayOutputStream();
			WritableWorkbook wwb = Workbook.createWorkbook(targetFile, wb);
			WritableSheet wws = null;

			for (int i = 0; i < sheetList.size(); i++) {

				ExcelSheet excelSheet = (ExcelSheet) sheetList.get(i);

				if (sheetList.size() == 1) {
					wws = wwb.getSheet(0);

				} else {
					wws = wwb.getSheet(i);
				}

				List headList = excelSheet.getHeaderList();
				if (headList == null || headList.size() == 0) {

				} else {
					for (int j = 0; j < headList.size(); j++) {

						ExcelCell cell = (ExcelCell) headList.get(j);

						Label label = new Label(cell.getX(), cell.getY(), cell
								.getValue());
						wws.addCell(label);
					}
				}

				List dataList = excelSheet.getBodyList();

				if (dataList != null) {

					int readLine = excelSheet.getFromRow();

					for (int k = 0; k < dataList.size(); k++) {

						String lineArr[] = (String[]) dataList.get(k);

						for (int m = 0; m < lineArr.length; m++) {
							Label cell = new Label(m, readLine + k, lineArr[m]);
							wws.addCell(cell);
						}
					}
				}

			}
			wwb.write();
			wwb.close();
			wb.close();
			targetFile.writeTo(os);
			targetFile.close();
			// os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 格式输出
	 * @param stencilStream
	 * @param sheetList
	 * @param os
	 */
	public void writeToExcelByStreamWithFormat(InputStream stencilStream,
			List sheetList, OutputStream os) {
		
		try {
			Workbook wb = Workbook.getWorkbook(stencilStream);
			
			ByteArrayOutputStream targetFile = new ByteArrayOutputStream();
			WritableWorkbook wwb = Workbook.createWorkbook(targetFile, wb);
			WritableSheet wws = null;
			
			for (int i = 0; i < sheetList.size(); i++) {
				
				ExcelSheet excelSheet = (ExcelSheet) sheetList.get(i);
				
				if (sheetList.size() == 1) {
					wws = wwb.getSheet(0);
					
				} else {
					wws = wwb.getSheet(i);
				}
				
				List headList = excelSheet.getHeaderList();
				if (headList == null || headList.size() == 0) {
					
				} else {
					for (int j = 0; j < headList.size(); j++) {
						
						ExcelCell cell = (ExcelCell) headList.get(j);
						
						Label label = new Label(cell.getX(), cell.getY(), cell
								.getValue());
						wws.addCell(label);
					}
				}
				
				List dataList = excelSheet.getBodyList();
				
				if (dataList != null) {
					
					int readLine = excelSheet.getFromRow();
					
					for (int k = 0; k < dataList.size(); k++) {
						
						String lineArr[] = (String[]) dataList.get(k);
						
						for (int m = 0; m < lineArr.length; m++) {
							CellFormat cellFormat = wws.getCell(m, readLine + k).getCellFormat();
							if(cellFormat != null) {
								Label cell = new Label(m, readLine + k, lineArr[m],cellFormat);
								wws.addCell(cell);
							}else {
								Label cell = new Label(m, readLine + k, lineArr[m]);
								wws.addCell(cell);
							}
						}
					}
				}
				
			}
			wwb.write();
			wwb.close();
			wb.close();
			targetFile.writeTo(os);
			targetFile.close();
			// os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * @param title
	 *            标题
	 * @param frmInfo
	 *            表单信息格式
	 * @param JSONObject
	 *            表单JSON对象值
	 * @param outputStream
	 *            输入流
	 * @param num
	 *            行显示列数
	 */
	public static void writeExcelForm(String title, String[][] frmInfo,
			JSONObject valueObj, int num, OutputStream outputStream)
			throws JSONException {

		try {
			WritableWorkbook writeWork = Workbook.createWorkbook(outputStream); // 新建工作薄
			WritableSheet outSheet = writeWork.createSheet("表单" + 1, 1); // 新建工作sheet

			// 标题
			if (title == null)
				title = "";
			WritableCellFormat titleFormat = ExcelFormat.getTitleFormat();
			outSheet.mergeCells(0, 0, 6 * num - 1, 1);
			Label lblTitle = new Label(0, 0, title, titleFormat);
			outSheet.addCell(lblTitle);
			outSheet.setRowView(0, 400);

			int row = 2;
			int col = 0;

			// 填写FORM表单
			if (frmInfo == null || frmInfo.length == 0 || valueObj == null)
				return;
			for (int i = 0; i < frmInfo.length; i++) {
				String stitle = frmInfo[i][0];
				String value;
				if (valueObj.has(frmInfo[i][1]) == false)
					value = "";
				else
					value = (valueObj.get(frmInfo[i][1]) == null) ? ""
							: valueObj.get(frmInfo[i][1]).toString();

				// 标题
				Label label = new Label(col, row, stitle, ExcelFormat
						.getSmallTitleFormat());
				outSheet.addCell(label);
				outSheet.mergeCells(col, row, col + 2, row);
				col = col + 3;

				// 值
				Label labelVal = new Label(col, row, value, ExcelFormat
						.getContentFormat());
				outSheet.addCell(labelVal);
				outSheet.mergeCells(col, row, col + 2, row);
				col = col + 3;

				if ((i + 1) % num == 0) {
					outSheet.setRowView(row, 400);
					row++;
					col = 0;
				}
			}

			// //填写Grid表头信息
			// col=0;
			// row++;
			// if(gridInfo==null||gridInfo.length==0)return;
			// for(int i = 0; i < gridInfo.length; i++) {
			// String stitle=gridInfo[i][0];
			//				
			// Label label = new Label(col++, row,
			// stitle,JExcelUtil.getSmallTitleFormat());
			// outSheet.addCell(label);
			// }
			//			

			writeWork.write();
			writeWork.close();
			outputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 根据传入的二维数组设定导出EXCEL列的宽度
	 * 
	 * @param list
	 * @param outputStream
	 * @param colwidthArr
	 *            列宽二维数组，第一维：sheet，第二维：列
	 */
	public static void writeExcelWithWidth(List list,
			OutputStream outputStream, int[][] colwithArr) {
		if (list == null || list.size() == 0) {
			return;
		}
		try {
			WritableWorkbook writeWork = Workbook.createWorkbook(outputStream); // 新建工作薄

			// body用格式:换行，有表格边框，垂直居中
			WritableCellFormat wrappedText = new WritableCellFormat(
					WritableWorkbook.ARIAL_10_PT);
			wrappedText.setWrap(true);
			wrappedText.setBorder(Border.ALL, BorderLineStyle.THIN);
			wrappedText.setVerticalAlignment(VerticalAlignment.CENTRE);

			for (int i = 0; i < list.size(); i++) {
				ExcelSheet sheet = (ExcelSheet) list.get(i);
				String sheetName = sheet.getSheetName();
				List headerList = sheet.getHeaderList();
				List bodyList = sheet.getBodyList();
				int fromRow = sheet.getFromRow();

				if (sheetName == null || "".equals(sheetName)) {
					sheetName = "sheet" + i;
				}

				WritableSheet outSheet = writeWork.createSheet(sheetName, i); // 新建工作sheet

				if (headerList != null) {
					for (int r = 0; r < headerList.size(); r++) {
						ExcelCell cell = (ExcelCell) headerList.get(r);
						Label lable = new Label(cell.getX(), cell.getY(), cell
								.getValue());
						outSheet.addCell(lable);
					}
				}

				if (bodyList != null) {
					for (int j = 0; j < bodyList.size(); j++) {
						String dataLine[] = (String[]) bodyList.get(j);

						for (int k = 0; k < dataLine.length; k++) {
							Label lable = new Label(k, j + fromRow,
									dataLine[k], wrappedText);
							//lable.setCellFormat(wrappedText);
							outSheet.addCell(lable);
							outSheet.setColumnView(k, colwithArr[i][k]);
						}
					}
				}
			}
			// outSheet.mergeCells(r,0,r,3); //合并单元格，参数格式（开始列，开始行，结束列，结束行）
			writeWork.write();
			writeWork.close();
			outputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) throws JSONException {
		String[] line = {
				"aaqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq",
				"bb", "cc" };
		ExcelCell cell = new ExcelCell();
		cell.setX(2);
		cell.setY(2);
		cell.setValue("AAAAAA");
		ExcelSheet sheet = new ExcelSheet();
		List headList = new ArrayList();
		List bodyList = new ArrayList();
		
		headList.add(cell);
		bodyList.add(line);
		sheet.setFromRow(10);
		sheet.setHeaderList(headList);
		sheet.setBodyList(bodyList);
		List sheetList = new ArrayList();
		sheetList.add(sheet);
		try {
			ExcelOperUtil util = new ExcelOperUtil();
			InputStream stream = new FileInputStream(new File("D:\\质保单模板.xls"));
			OutputStream os = new FileOutputStream(new File("D:\\testAAAA.xls"));
			util.writeToExcelByStencil(stream, sheetList, os);

		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		/* 输出表单 */

		/**
		 * 对齐方式 WritableCellFormat titleFormat = ExcelFormat.getTitleFormat();
		 * titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE); //垂直对齐方式
		 * titleFormat.setAlignment(Alignment.CENTRE); //横向对齐方式
		 */

		// 定义表单标题
		// String title="物资暂存单";
		//		
		// //定义表单信息（二维数组，标题+键值）
		// String[][]frmInfo={
		// {"供应商","LTEXT"},
		// {"供应商合同编号","EBELN"},
		// {"配送单号","ZPSDH"},
		// {"流程状态","STATUS"},
		// {"申请库存地点","ZKCDD"},
		// {"申请暂存时限","ZZCRQ"},
		// {"申请暂存地点","ZZCDD"},
		// {"申请暂存理由","ZZCLY"}
		// };
		//
		// //定义组成JSON对象信息
		// JSONObject jsObj=new JSONObject();
		// jsObj.put("LTEXT", "值1");
		// jsObj.put("EBELN", "值2");
		// jsObj.put("ZPSDH", "值3");
		// jsObj.put("STATUS", "值4");
		// jsObj.put("ZKCDD", "值5");
		// jsObj.put("ZZCRQ", "值6");
		// jsObj.put("ZZCDD", "值7");
		// jsObj.put("ZZCLY", "值8");
		//		
		// //定义一行显示几列
		// int num=2;
		//		
		// try {
		// OutputStream os = new FileOutputStream(new File("D:\\test.xls"));
		// writeExcelForm(title,frmInfo,jsObj,num,os);
		// } catch (FileNotFoundException e) {
		// e.printStackTrace();
		// }

		/*
		 * 输出LIST String[] line =
		 * {"aaqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq",
		 * "bb", "cc"}; ExcelSheet sheet = new ExcelSheet(); List bodyList = new
		 * ArrayList(); bodyList.add(line); sheet.setBodyList(bodyList); List
		 * sheetList = new ArrayList(); sheetList.add(sheet);
		 * sheetList.add(sheet);
		 * 
		 * try { OutputStream os = new FileOutputStream(new
		 * File("D:\\test.xls")); writeExcel(sheetList, os); } catch
		 * (FileNotFoundException e) { // TODO Auto-generated catch block
		 * e.printStackTrace(); }
		 */
	}

}
